from sqlmodel import select
from sqlalchemy.orm import joinedload, selectinload
from app import  models,crud
from typing import Optional
from app.core.security import get_password_hash
from fastapi import status
from .base import BaseService
from app.utils import generate_verification_code,send_sms
from datetime import datetime,timedelta
import logging
from sqlmodel import SQLModel, Field, create_engine, Session, select, text
class FootballService(BaseService):    

    async def Search_matches_by_time(self, payload: models.SearchCondition) -> models.MatchesResultResponse:
        # 构建 WHERE 条件
        where_conditions = [f"date(sf.matchtime) between '{payload.s_time}' and '{payload.e_time}'"]
        
        # 如果提供了 event_name，添加到 WHERE 条件中
        if payload.event_name:
            where_conditions.append(f"sf.event_name = '{payload.event_name}'")
        
        where_clause = " and ".join(where_conditions)
        
        GET_MATCHES_SQL=f"""
        select 
event_name
,fid
,homesxname
,awaysxname
,matchtime
,homescore
,awayscore from t_football_signal_final sf
where {where_clause}
order by sf.event_name,sf.matchtime 
        """
        result =self.db.exec(text(GET_MATCHES_SQL))
        matches = []
        for row in result.all():
            match_dict = row._asdict()
            # 保持datetime对象，让Pydantic自动处理序列化
            matches.append(models.Matches(**match_dict))
        
        if len(matches)==0:
            return  models.MatchesResultResponse(code=status.HTTP_404_NOT_FOUND, message="没有找到合适的比赛",data=None)
        return  models.MatchesResultResponse(code=0, message="检索成功",data=matches)
    async def get_signal(
        self,req:models.SignalCondition
    ) -> models.SignalResultResponse:
        if req.company_id==293:
            m=models.Signal_293
        elif req.company_id==18:
            m=models.Signal_18    
        stmt = select(m).where(m.fid == req.fid)
        result = self.db.exec(stmt)
        result= result.all()
        if len(result)==0:
            return models.SignalResultResponse(code=status.HTTP_404_NOT_FOUND,
                message="没有发现合适的比赛",data=None)
                
        # 使用列表推导式直接转换所有结果
        matches = [
            {
                **row.model_dump(),
                'odds_time': row.odds_time.strftime('%Y-%m-%d %H:%M:%S') #datetime.strptime(row.odds_time, '%Y%m%d%H').strftime('%Y-%m-%d %H:%M:%S') if row.odds_time else None
            }
            for row in result
        ]
        return models.SignalResultResponse(code=0,
                message="查询成功",data=matches)

    async def get_event_list(self) -> models.EventListResultResponse:
        GET_EVENT_LIST_SQL = """
        select event_name from t_football_signal_final
        group by event_name
        order by count(*) desc
        """
        result = self.db.exec(text(GET_EVENT_LIST_SQL))
        events = []
        for row in result.all():
            event_dict = row._asdict()
            events.append(models.EventList(**event_dict))
        
        if len(events) == 0:
            return models.EventListResultResponse(code=status.HTTP_404_NOT_FOUND, message="没有找到赛事", data=None)
        return models.EventListResultResponse(code=0, message="检索成功", data=events)

    async def get_similar_matches(self, payload: models.SimilarMatchesCondition) -> models.SimilarMatchesResultResponse:
        # 构建 WHERE 条件
        where_conditions = []
        
        # 如果提供了 event_name，添加到 WHERE 条件中
        if payload.event_name:
            where_conditions.append(f"mia.event_name = '{payload.event_name}'")
        
        # 添加赔率和信号范围条件
        where_conditions.extend([
            f"mia.home_initial between {payload.home_initial_min} and {payload.home_initial_max}",
            f"mia.draw_initial between {payload.draw_initial_min} and {payload.draw_initial_max}",
            f"mia.away_initial between {payload.away_initial_min} and {payload.away_initial_max}",
            f"mia.signal_home_initial between {payload.signal_home_initial_min} and {payload.signal_home_initial_max}",
            f"mia.signal_draw_initial between {payload.signal_draw_initial_min} and {payload.signal_draw_initial_max}",
            f"mia.signal_away_initial between {payload.signal_away_initial_min} and {payload.signal_away_initial_max}"
        ])
        
        where_clause = " and ".join(where_conditions)
        
        # 首先查询总记录数
        COUNT_SQL = f"""
        select count(*) as total from t_euro_odds_signal_matches mia
        where {where_clause}
        """
        count_result = self.db.exec(text(COUNT_SQL)).first()
        total = count_result.total if count_result else 0
        
        if total == 0:
            return models.SimilarMatchesResultResponse(
                code=status.HTTP_404_NOT_FOUND, 
                message="没有找到相似比赛", 
                data=None,
                pagination=models.PaginationInfo(
                    current_page=payload.current_page,
                    page_size=payload.page_size,
                    total=0,
                    total_pages=0
                ),
                statistics=None
            )
        
        # 计算分页参数
        offset = (payload.current_page - 1) * payload.page_size
        total_pages = (total + payload.page_size - 1) // payload.page_size
        
        # 查询分页数据
        GET_SIMILAR_MATCHES_SQL = f"""
        select mia.event_name 
        ,mia.fid
        ,mia.order_num
        ,mia.matchtime 
        ,mia.homesxname 
        ,mia.awaysxname 
        ,mia.homescore 
        ,mia.awayscore 
        ,mia.home_initial
        ,mia.draw_initial
        ,mia.away_initial
        ,mia.home_final
        ,mia.draw_final
        ,mia.away_final
        ,mia.signal_home_initial
        ,mia.signal_draw_initial
        ,mia.signal_away_initial
        ,mia.signal_home_final
        ,mia.signal_draw_final
        ,mia.signal_away_final from t_euro_odds_signal_matches mia
        where {where_clause}
        order by mia.matchtime desc
        limit {payload.page_size} offset {offset}
        """
        result = self.db.exec(text(GET_SIMILAR_MATCHES_SQL))
        matches = []
        for row in result.all():
            match_dict = row._asdict()
            matches.append(models.SimilarMatches(**match_dict))
        
        # 查询统计信息
        STATISTICS_SQL = f"""
        SELECT 
            COUNT(*) AS total_matches,
            SUM(CASE WHEN mia.homescore > mia.awayscore THEN 1 ELSE 0 END) AS home_wins,
            SUM(CASE WHEN mia.homescore = mia.awayscore THEN 1 ELSE 0 END) AS draws,
            SUM(CASE WHEN mia.homescore < mia.awayscore THEN 1 ELSE 0 END) AS away_wins,
            ROUND(SUM(CASE WHEN mia.homescore > mia.awayscore THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS home_win_rate,
            ROUND(SUM(CASE WHEN mia.homescore = mia.awayscore THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS draw_rate,
            ROUND(SUM(CASE WHEN mia.homescore < mia.awayscore THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS away_win_rate
        FROM t_euro_odds_signal_matches mia
        WHERE {where_clause}
        """
        stats_result = self.db.exec(text(STATISTICS_SQL)).first()
        statistics = models.MatchStatistics(**stats_result._asdict())
        
        return models.SimilarMatchesResultResponse(
            code=0, 
            message="检索成功", 
            data=matches,
            pagination=models.PaginationInfo(
                current_page=payload.current_page,
                page_size=payload.page_size,
                total=total,
                total_pages=total_pages
            ),
            statistics=statistics
        )
